<?php
namespace app\index\controller;
use think\Controller;
use think\Session;
use think\Request;
use think\View;
use think\Db;
use think\Model;
use PHPExcel;
use PHPExcel_IOFactory;
//经销商目标
class Jingxiaomubiao extends Yusuandaoru
{
            //预算明细
    public function zuofei($tid,$from=1){
        $this->assign("from",$from);
        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.state2,t.tag,t.fujian")
        ->find();
        $this->assign("apply",$apply);
         $fujian=[];
            //print_r($apply);
            if($apply["fujian"]){
                $fujian=Db::table("annex")->where("id in ({$apply["fujian"]})")->select();
                //print_r($fujian);
            }
            $this->assign("fujian",$fujian);
             if($apply["state2"]==1)
                $fid=8;
            else
                $fid=9;
         //明细
        $detail=Db::table("target_apply_detail")->alias("t")
        ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","t.channel2=c2.id","LEFT")
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
        ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
        ->join("cg_market_city ac","t.shi=ac.id","LEFT")
        ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,c1.title as channel1,c2.title as channel2,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
        
            //获取财年和考核期
        $ck=Db::table("target_apply_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_apply_detail as t inner join cg_cainian as c on t.cainian=c.id inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id where t.apply_id=$tid group by t.cainian,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);

        //汇总
        $huizong=Db::table("target_apply_detail")->query("select j.title as jingxiao,j.id as jid,pb.brand_name as pinpai,pb.id as pid,sum(feiyong) as f  from target_apply_detail as t inner join pro_brands as pb on t.pinpai=pb.id inner join cg_jxs as j on t.jingxiao=j.id where apply_id=$tid  group by t.jingxiao,t.pinpai");
        $huizongde=Db::table("target_apply_detail")->query("select cainian,yijikaoheqi,jingxiao,pinpai,sum(feiyong) as f,erjikaoheqi  from target_apply_detail where apply_id=$tid  group by jingxiao,pinpai,cainian,yijikaoheqi,erjikaoheqi");
        $sumhuizong=0;
        for($i=0;$i<count($huizongde);$i++){
            $sumhuizong+=$huizongde[$i]["f"];
            if($huizongde[$i]["erjikaoheqi"]){
                $huizongde[$i]["kaoheqi"]=$huizongde[$i]["erjikaoheqi"];
            }else{
                $huizongde[$i]["kaoheqi"]=$huizongde[$i]["yijikaoheqi"];
            }
        }

        for($i=count($huizong)-1;$i>=0;$i--){
            $huizong[$i]["hang"]=1;
            if($i<count($huizong)-1){
                //合并单元格
                if($huizong[$i]["jingxiao"]==$huizong[$i+1]["jingxiao"]){
                    
                    $huizong[$i]["hang"]=$huizong[$i]["hang"]+$huizong[$i+1]["hang"];
                    $huizong[$i+1]["hang"]=0;
                }
            }
        }
        $this->assign("huizong",$huizong);
        $this->assign("huizongde",$huizongde);
        $this->assign("sumhuizong",$sumhuizong);
         $btn=$this->getbton($fid,$tid);
        $this->assign("btn",$btn);
         if($apply["state2"]==1)
            $logs=$this->getlog(8,$tid);
        else
             $logs=$this->getlog(9,$tid);
        //print_r($logs);
        $this->assign("logs",$logs);
        if($apply["state2"]==2){
             $yuandetail=Db::table("target_apply_detail")->alias("t")
            ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
            ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("j.title as jingxiao,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,t.feiyong")
            ->select();
             $tongjidata=[];
             for($i=0;$i<count($detail);$i++){
                
                $tongji["jingxiao"]=$detail[$i]["jingxiao"];$tongji["pinpai"]=$detail[$i]["pinpai"];$tongji["pinlei"]=$detail[$i]["pinlei"];
                $tongji["xilie"]=$detail[$i]["xilie"];$tongji["yijikaoheqi"]=$detail[$i]["yijikaoheqi"];$tongji["erjikaoheqi"]=$detail[$i]["erjikaoheqi"];
                $tongji["feiyong"]=$detail[$i]["feiyong"];$tongji["cainian"]=$detail[$i]["cainian"];
                $tongji["yuanfeiyong"]=0;
                 for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                            //内容相似
                            $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                            
                            array_splice($yuandetail, $j, 1);break;
                        }
                      
                 }
                 $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                 $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"]];
                $tongjidata[]=$tongji;
            }
            //print_r($tongjidata);exit;
            $this->assign("tongjidata",$tongjidata);
        }
        return      $this->fetch();

    }
    //预算明细
    public function yidong($tid,$from=1){
        $this->assign("from",$from);
        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.state2,t.tag,t.fujian")
        ->find();
        $this->assign("apply",$apply);
         $fujian=[];
            //print_r($apply);
            if($apply["fujian"]){
                $fujian=Db::table("annex")->where("id in ({$apply["fujian"]})")->select();
                //print_r($fujian);
            }
            $this->assign("fujian",$fujian);

         //明细
        $detail=Db::table("target_apply_detail")->alias("t")
        ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","t.channel2=c2.id","LEFT")
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
        ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
        ->join("cg_market_city ac","t.shi=ac.id","LEFT")
        ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,c1.title as channel1,c2.title as channel2,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
        
            //获取财年和考核期
        $ck=Db::table("target_apply_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_apply_detail as t inner join cg_cainian as c on t.cainian=c.id inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id where t.apply_id=$tid group by t.cainian,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);

        //汇总
        $huizong=Db::table("target_apply_detail")->query("select j.title as jingxiao,j.id as jid,pb.brand_name as pinpai,pb.id as pid,sum(feiyong) as f  from target_apply_detail as t inner join pro_brands as pb on t.pinpai=pb.id inner join cg_jxs as j on t.jingxiao=j.id where apply_id=$tid  group by t.jingxiao,t.pinpai");
        $huizongde=Db::table("target_apply_detail")->query("select cainian,yijikaoheqi,jingxiao,pinpai,sum(feiyong) as f,erjikaoheqi  from target_apply_detail where apply_id=$tid  group by jingxiao,pinpai,cainian,yijikaoheqi,erjikaoheqi");
        $sumhuizong=0;
        for($i=0;$i<count($huizongde);$i++){
            $sumhuizong+=$huizongde[$i]["f"];
            if($huizongde[$i]["erjikaoheqi"]){
                $huizongde[$i]["kaoheqi"]=$huizongde[$i]["erjikaoheqi"];
            }else{
                $huizongde[$i]["kaoheqi"]=$huizongde[$i]["yijikaoheqi"];
            }
        }

        for($i=count($huizong)-1;$i>=0;$i--){
            $huizong[$i]["hang"]=1;
            if($i<count($huizong)-1){
                //合并单元格
                if($huizong[$i]["jingxiao"]==$huizong[$i+1]["jingxiao"]){
                    
                    $huizong[$i]["hang"]=$huizong[$i]["hang"]+$huizong[$i+1]["hang"];
                    $huizong[$i+1]["hang"]=0;
                }
            }
        }
        $this->assign("huizong",$huizong);
        $this->assign("huizongde",$huizongde);
        $this->assign("sumhuizong",$sumhuizong);
         if($apply["state2"]==1)
            $logs=$this->getlog(8,$tid);
        else
             $logs=$this->getlog(9,$tid);
        //print_r($logs);
        $this->assign("logs",$logs);
        return      $this->fetch();

    }
    //异动预算
    public function yd_jt_add(){
        $request = Request::instance();

        
        $data=$request->post();
        //print_r($data);exit;
        $target=Db::table("target_apply")->where(["id"=>$data['tid']])->find();
        $biaoti=$target["title"];
        $remark=trim(htmlspecialchars($data["remark"]));
        $state=trim($data["type"]);
        //$shenpiren=trim($data["shenpiren"]);
        $file = request()->file('wenjian');
        $tid=$data['tid'];
        if($data["attach"])
            $fujian=$data["attach"];
        else
            $fujian=null;
        if($file){
            $info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/Jingxiaotuan' . DS );
            if($info){
                // 成功上传后 获取上传信息
               
                // 输出
                $savename= $info->getSaveName();
                $path=ROOT_PATH . 'public/uploads/Jingxiaotuan/'.$savename;
                $res=$this->excel_yidong("经销商销售目标格式",$tid,$state,$biaoti,$remark,$path,$fujian);
                return $res;

                
            }else{
                // 上传失败获取错误信息
                return json_encode(["msg"=>$file->getError(),"state"=>2]);
               
            }
        }else{
             return json_encode(["msg"=>"文件不能为空","state"=>2]);
        }

    }
     public function xg_jt_add(){
        $request = Request::instance();

        
        $data=$request->post();
       // print_r($data);exit;
           $target=Db::table("target_apply")->where(["id"=>$data['tid']])->find();
        $biaoti=trim($data["title"]);
        $remark=trim(htmlspecialchars($data["remark"]));
        $state=trim($data["type"]);
       // $shenpiren=trim($data["shenpiren"]);
        $file = request()->file('wenjian');
        $tid=$data['tid'];
        if($data["attach"])
            $fujian=$data["attach"];
        else
            $fujian=null;
        $path=null;
         if($file){
            $info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/Jingxiaotuan' . DS );
            if($info){
                // 成功上传后 获取上传信息
               
                // 输出
                $savename= $info->getSaveName();
                $path=ROOT_PATH . 'public/uploads/Jingxiaotuan/'.$savename;
                $res=$this->excel_xiugai("经销商销售目标格式",$tid,$path);
                $res=json_decode($res,true);
                
            }else{
                // 上传失败获取错误信息
                return json_encode(["msg"=>$file->getError(),"state"=>2]);
               
            }
        }else{
            $res=["state"=>1,"msg"=>""];
        }

        if($res["state"]==1){
            try{
                 if($path){
                 $res1=Db::table("target_apply")->where(["id"=>$tid])->update(["title"=>$biaoti,"remark"=>$remark,"fujian"=>$fujian,"state"=>$state,"path"=>$path]);
                }else{
                     $res1=Db::table("target_apply")->where(["id"=>$tid])->update(["title"=>$biaoti,"remark"=>$remark,"fujian"=>$fujian,"state"=>$state]);
                }
            }catch(\Exception $e){
             return json_encode(["msg"=>"系统繁忙请稍后再试","state"=>2]);
            }
             if($state==3){
                $backjson=$this->ystijiao($tid);
                return json_encode($res);
            }else
                return json_encode($res);
        }else{
             return json_encode($res);
        }
            
            
    }

            //预算明细
    public function xiugai($tid,$from=1){
        $this->assign("from",$from);
        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.state2,t.tag,t.fujian")
        ->find();
        $this->assign("apply",$apply);
         $fujian=[];
            //print_r($apply);
            if($apply["fujian"]){
                $fujian=Db::table("annex")->where("id in ({$apply["fujian"]})")->select();
                //print_r($fujian);
            }
            $this->assign("fujian",$fujian);

         //明细
        $detail=Db::table("target_apply_detail")->alias("t")
        ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","t.channel2=c2.id","LEFT")
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
        ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
        ->join("cg_market_city ac","t.shi=ac.id","LEFT")
        ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,c1.title as channel1,c2.title as channel2,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
        
            //获取财年和考核期
        $ck=Db::table("target_apply_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_apply_detail as t inner join cg_cainian as c on t.cainian=c.id inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id where t.apply_id=$tid group by t.cainian,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);

        //汇总
        $huizong=Db::table("target_apply_detail")->query("select j.title as jingxiao,j.id as jid,pb.brand_name as pinpai,pb.id as pid,sum(feiyong) as f  from target_apply_detail as t inner join pro_brands as pb on t.pinpai=pb.id inner join cg_jxs as j on t.jingxiao=j.id where apply_id=$tid  group by t.jingxiao,t.pinpai");
        $huizongde=Db::table("target_apply_detail")->query("select cainian,yijikaoheqi,jingxiao,pinpai,sum(feiyong) as f,erjikaoheqi  from target_apply_detail where apply_id=$tid  group by jingxiao,pinpai,cainian,yijikaoheqi,erjikaoheqi");
        $sumhuizong=0;
        for($i=0;$i<count($huizongde);$i++){
            $sumhuizong+=$huizongde[$i]["f"];
            if($huizongde[$i]["erjikaoheqi"]){
                $huizongde[$i]["kaoheqi"]=$huizongde[$i]["erjikaoheqi"];
            }else{
                $huizongde[$i]["kaoheqi"]=$huizongde[$i]["yijikaoheqi"];
            }
        }

        for($i=count($huizong)-1;$i>=0;$i--){
            $huizong[$i]["hang"]=1;
            if($i<count($huizong)-1){
                //合并单元格
                if($huizong[$i]["jingxiao"]==$huizong[$i+1]["jingxiao"]){
                    
                    $huizong[$i]["hang"]=$huizong[$i]["hang"]+$huizong[$i+1]["hang"];
                    $huizong[$i+1]["hang"]=0;
                }
            }
        }
        $this->assign("huizong",$huizong);
        $this->assign("huizongde",$huizongde);
        $this->assign("sumhuizong",$sumhuizong);
        
        return      $this->fetch();

    }
      //导出
     public function daochu($tid){
         $apply=Db::table("target_apply")->where(["id"=>$tid])->find();
         $file_path=$apply["path"];$file_name="经销商销售目标-".$apply["title"].".xls";
         header("Content-type:text/html;charset=utf-8"); 
         
           
            if(!file_exists($file_path)){ 
            echo "没有该文件文件"; 
            return ; 
            } 
            $fp=fopen($file_path,"r"); 
            $file_size=filesize($file_path); 
            //下载文件需要用到的头 
            Header("Content-type: application/octet-stream"); 
            Header("Accept-Ranges: bytes"); 
            Header("Accept-Length:".$file_size); 
            Header("Content-Disposition: attachment; filename=".$file_name); 
               ob_clean();
            flush();
            $buffer=1024; 
            $file_count=0; 
            //向浏览器返回数据 
            while(!feof($fp) && $file_count<$file_size){ 
            $file_con=fread($fp,$buffer); 
            $file_count+=$buffer; 
            echo $file_con; 
            } 
            fclose($fp); 
     }
    
	//所有经销商管理
	public function allmanage($page=1,$list=10){
		//获取编号
		$typename="经销商销售目标格式";
		$tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
		$type_id=$tmp["id"];
		//接收数据
    	$request = Request::instance();

    	
    	$data=$request->post();

    	 $user_id=Session::get("login_id");
        $where="t.is_actived=1 and t.is_deleted=0 and t.type_id=$type_id and t.c_users_id=$user_id ";
    	if($data){
            if(trim($data["no"]))
                $where.="and t.no like '%{$data["no"]}%' ";
            if(trim($data["title"]))
                $where.="and t.title like '%{$data["title"]}%' ";
            if(trim($data["start"]))
                $where.="and t.created_on>='{$data["start"]} 00:00:00' ";
            if(trim($data["end"]))
                $where.="and t.created_on<='{$data["end"]} 23:59:59' ";
             if(trim($data["state"]))
                $where.="and t.state={$data["state"]} ";
        }else{
            $data=["no"=>"","title"=>"","start"=>"","end"=>"","state"=>0];
        }
    	//数量
    	$sum=Db::table("target_apply")->alias("t")->where($where)->count();
    	$maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

       $apply=Db::table("target_apply")->alias("t")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("ti_state ts","t.state=ts.id")
        ->where($where)->order("created_on desc,no desc")
        ->limit($start,$list)
        ->field("t.id,t.no,t.title,t.state,ec.em_name as cname,t.created_on,ts.title as state_name,t.tag,t.state2,t.islock")
        ->select();

         for($i=0;$i<count($apply);$i++){
            $apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>8])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>9])->value("id");
            
        }
        $this->assign("sum",$sum);
		$this->assign("page",$page);
		$this->assign("list",$list);
		$this->assign("maxpage",$maxpage);
		$this->assign("data",$data);
		$this->assign("apply",$apply);
         $statearr=Db::table("ti_state")->select();
        $this->assign("statearr",$statearr);
		return $this->fetch('allmanage');
	}
//所有经销商管理
    public function allshumanage($page=1,$list=10){
        //获取编号
        $typename="经销商销售目标格式";
        $tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //接收数据
        $request = Request::instance();

        
        $data=$request->post();

         $user_id=Session::get("login_id");
        $where="t.is_actived=1 and t.is_deleted=0 and t.type_id=$type_id and t.c_users_id=$user_id ";
        if($data){
            if(trim($data["no"]))
                $where.="and t.no like '%{$data["no"]}%' ";
            if(trim($data["title"]))
                $where.="and t.title like '%{$data["title"]}%' ";
            if(trim($data["start"]))
                $where.="and t.created_on>='{$data["start"]} 00:00:00' ";
            if(trim($data["end"]))
                $where.="and t.created_on<='{$data["end"]} 23:59:59' ";
             if(trim($data["state"]))
                $where.="and t.state={$data["state"]} ";
        }else{
            $data=["no"=>"","title"=>"","start"=>"","end"=>"","state"=>0];
        }
        //数量
        $sum=Db::table("target_apply")->alias("t")->where($where)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

       $apply=Db::table("target_apply")->alias("t")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("ti_state ts","t.state=ts.id")
        ->where($where)->order("created_on desc,no desc")
        ->limit($start,$list)
        ->field("t.id,t.no,t.title,t.state,ec.em_name as cname,t.created_on,ts.title as state_name,t.tag,t.state2,t.islock")
        ->select();

         for($i=0;$i<count($apply);$i++){
            $apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>8])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>9])->value("id");
            
        }
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
         $statearr=Db::table("ti_state")->select();
        $this->assign("statearr",$statearr);
        return $this->fetch('allshumanage');
    }
	//新增经销商目标
	public function new_jx(){
		return $this->fetch('new_jx');
	}
		//保存预算
	public function new_jx_add(){
		$request = Request::instance();

    	
    	$data=$request->post();
    	$data=$request->post();
        $biaoti=trim(htmlspecialchars($data["title"]));
        if(!$biaoti){
             return json_encode(["msg"=>"主题不能为空","state"=>2]);
        }
        $remark=trim(htmlspecialchars($data["remark"]));
        $state=trim($data["type"]);
        $fujian=null;
        if($data["attach"])
            $fujian=$data["attach"];
        $file = request()->file('wenjian');

    	if($file){
    		$info = $file->validate(['size'=>2*1024*1024,'ext'=>'xls'])->move(ROOT_PATH . 'public/uploads/Jingxiaotuan' . DS );
	    	if($info){
	            // 成功上传后 获取上传信息
	           
	            // 输出
	            $savename= $info->getSaveName();
	            $path=ROOT_PATH . 'public/uploads/Jingxiaotuan/'.$savename;
	          	$res=$this->excel_duqu("经销商销售目标格式",$state,$biaoti,$remark,$path,$fujian);
	          	return $res;

	          	
	        }else{
	            // 上传失败获取错误信息
	            return json_encode(["msg"=>$file->getError(),"state"=>2]);
	           
	        }
    	}else{
    		 return json_encode(["msg"=>"文件不能为空","state"=>2]);
    	}
    	
    	//echo 1;exit;
		//print_r($data);exit;
	}
	//所有经销商查询
	public function allck($page=1,$list=10){
		//获取编号
        $typename="经销商销售目标格式";
        $tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //接收数据
        $request = Request::instance();

        
        $data=$request->post();

         $user_id=Session::get("login_id");
        $where="t.is_actived=1 and t.is_deleted=0 and t.type_id=$type_id and t.c_users_id=$user_id ";
        if($data){
            if(trim($data["no"]))
                $where.="and t.no like '%{$data["no"]}%' ";
            if(trim($data["title"]))
                $where.="and t.title like '%{$data["title"]}%' ";
            if(trim($data["start"]))
                $where.="and t.created_on>='{$data["start"]} 00:00:00' ";
            if(trim($data["end"]))
                $where.="and t.created_on<='{$data["end"]} 23:59:59' ";
             if(trim($data["state"]))
                $where.="and t.state={$data["state"]} ";
        }else{
            $data=["no"=>"","title"=>"","start"=>"","end"=>"","state"=>0];
        }
        //数量
        $sum=Db::table("target_apply")->alias("t")->where($where)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

       $apply=Db::table("target_apply")->alias("t")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("ti_state ts","t.state=ts.id")
        ->where($where)->order("created_on desc,no desc")
        ->limit($start,$list)
        ->field("t.id,t.no,t.title,t.state,ec.em_name as cname,t.created_on,ts.title as state_name,t.tag,t.state2,t.islock")
        ->select();

         for($i=0;$i<count($apply);$i++){
            $apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>8])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>9])->value("id");
            
        }
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
         $statearr=Db::table("ti_state")->select();
        $this->assign("statearr",$statearr);
        return $this->fetch();
	}
    //所有经销商查询
    public function allshuck($page=1,$list=10){
        //获取编号
        $typename="经销商销售目标格式";
        $tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //接收数据
        $request = Request::instance();

        
        $data=$request->post();

         $user_id=Session::get("login_id");
        $where="t.is_actived=1 and t.is_deleted=0 and t.type_id=$type_id and t.c_users_id=$user_id ";
        if($data){
            if(trim($data["no"]))
                $where.="and t.no like '%{$data["no"]}%' ";
            if(trim($data["title"]))
                $where.="and t.title like '%{$data["title"]}%' ";
            if(trim($data["start"]))
                $where.="and t.created_on>='{$data["start"]} 00:00:00' ";
            if(trim($data["end"]))
                $where.="and t.created_on<='{$data["end"]} 23:59:59' ";
             if(trim($data["state"]))
                $where.="and t.state={$data["state"]} ";
        }else{
            $data=["no"=>"","title"=>"","start"=>"","end"=>"","state"=>0];
        }
        //数量
        $sum=Db::table("target_apply")->alias("t")->where($where)->count();
        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;

       $apply=Db::table("target_apply")->alias("t")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("ti_state ts","t.state=ts.id")
        ->where($where)->order("created_on desc,no desc")
        ->limit($start,$list)
        ->field("t.id,t.no,t.title,t.state,ec.em_name as cname,t.created_on,ts.title as state_name,t.tag,t.state2,t.islock")
        ->select();

         for($i=0;$i<count($apply);$i++){
            $apply[$i]["created_on"]=date("Y-m-d H:i",strtotime($apply[$i]["created_on"]));
            if($apply[$i]["state2"]==1)
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>8])->value("id");
            else
                $apply[$i]["myId"]=Db::table("cg_workflow")->where(["yw_id"=>$apply[$i]["id"],"ywlx"=>9])->value("id");
            
        }
        $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
         $statearr=Db::table("ti_state")->select();
        $this->assign("statearr",$statearr);
        return $this->fetch();
    }
		//预算明细
	public function detail($tid){

		$apply=Db::table("target_apply")->alias("t")
        ->join("target_apply yuant","t.yuanid=yuant.id","LEFT")
        ->join("target_apply nextt","t.nextid=nextt.id","LEFT")
        ->join("users uc","t.c_users_id=uc.id","LEFT")
        ->join("employee ec","uc.employee_id=ec.id","LEFT")
        ->join("department_employee de","ec.id=de.employee_id","LEFT")
        ->join("department d","de.department_id=d.id","LEFT")->where(["t.id"=>$tid])
        ->field("t.id,t.title,t.no,yuant.no as yuanno,nextt.no as nextno,yuant.id as yuanid,nextt.id as nextid,t.state,ec.em_name as cname,t.created_on,d.de_name,t.remark,t.state2,t.tag,t.fujian")
        ->find();
        $this->assign("apply",$apply);
         $fujian=[];
            //print_r($apply);
            if($apply["fujian"]){
                $fujian=Db::table("annex")->where("id in ({$apply["fujian"]})")->select();
                //print_r($fujian);
            }
            $this->assign("fujian",$fujian);

		 //明细
        $detail=Db::table("target_apply_detail")->alias("t")
        ->join("cg_channel_types1 c1","t.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","t.channel2=c2.id","LEFT")
        ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
        ->join("cg_system x","t.xitong=x.id","LEFT")
        ->join("cg_net n","t.wangdian=n.id","LEFT")
        ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
        ->join("pro_category pc","t.pinlei=pc.id","LEFT")
        ->join("pro_series ps","t.xilie=ps.id","LEFT")
        ->join("cg_market_zoning af","t.fenqu=af.id","LEFT")
        ->join("cg_market_provice ap","t.sheng=ap.id","LEFT")
        ->join("cg_market_city ac","t.shi=ac.id","LEFT")
        ->join("cg_feiyongkemu1 f1","t.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","t.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","t.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
        ->where(["t.apply_id"=>$tid])
        ->field("t.detail_no,c1.title as channel1,c2.title as channel2,j.title as jingxiao,x.title as xitong,n.title as wangdian,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,af.title as fenqu,ap.title as sheng,ac.title as shi,f1.title as yijifeiyong,f2.title as erjifeiyong,f3.title as sanjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong")
        ->select();
        $this->assign("detail",$detail);
		
			//获取财年和考核期
		$ck=Db::table("target_apply_detail")->query("select c.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,c.id as cid,k1.id as k1id,k2.id as k2id  from target_apply_detail as t inner join cg_cainian as c on t.cainian=c.id inner join cg_kaoheqi1 as k1 on t.yijikaoheqi=k1.id LEFT join cg_kaoheqi2 as k2 on t.erjikaoheqi=k2.id where t.apply_id=$tid group by t.cainian,t.yijikaoheqi,t.erjikaoheqi,c.id,k1.id,k2.id ");
        for($i=0;$i<count($ck);$i++){
            if($ck[$i]["erjikaoheqi"]){
                $ck[$i]["kaoheqi"]=$ck[$i]["erjikaoheqi"];
                $ck[$i]["kid"]=$ck[$i]["k2id"];
            }else{
                $ck[$i]["kaoheqi"]=$ck[$i]["yijikaoheqi"]; $ck[$i]["kid"]=$ck[$i]["k1id"];
            }
        }
        $this->assign("ck",$ck);
        //考核期数量
        $kc=count($ck)+1;
    
        $this->assign("kc",$kc);

		//汇总
		$huizong=Db::table("target_apply_detail")->query("select j.title as jingxiao,j.id as jid,pb.brand_name as pinpai,pb.id as pid,sum(feiyong) as f  from target_apply_detail as t inner join pro_brands as pb on t.pinpai=pb.id inner join cg_jxs as j on t.jingxiao=j.id where apply_id=$tid  group by t.jingxiao,t.pinpai");
		$huizongde=Db::table("target_apply_detail")->query("select cainian,yijikaoheqi,jingxiao,pinpai,sum(feiyong) as f,erjikaoheqi  from target_apply_detail where apply_id=$tid  group by jingxiao,pinpai,cainian,yijikaoheqi,erjikaoheqi");
		$sumhuizong=0;
		for($i=0;$i<count($huizongde);$i++){
			$sumhuizong+=$huizongde[$i]["f"];
			if($huizongde[$i]["erjikaoheqi"]){
				$huizongde[$i]["kaoheqi"]=$huizongde[$i]["erjikaoheqi"];
			}else{
				$huizongde[$i]["kaoheqi"]=$huizongde[$i]["yijikaoheqi"];
			}
		}

		for($i=count($huizong)-1;$i>=0;$i--){
			$huizong[$i]["hang"]=1;
			if($i<count($huizong)-1){
				//合并单元格
				if($huizong[$i]["jingxiao"]==$huizong[$i+1]["jingxiao"]){
					
					$huizong[$i]["hang"]=$huizong[$i]["hang"]+$huizong[$i+1]["hang"];
					$huizong[$i+1]["hang"]=0;
				}
			}
		}
		$this->assign("huizong",$huizong);
		$this->assign("huizongde",$huizongde);
		$this->assign("sumhuizong",$sumhuizong);
         if($apply["state2"]==1)
            $logs=$this->getlog(8,$tid);
        else
             $logs=$this->getlog(9,$tid);
        //print_r($logs);
        $this->assign("logs",$logs);
        if($apply["state2"]==2){
             $yuandetail=Db::table("target_apply_detail")->alias("t")
            ->join("cg_cainian cn","t.cainian=cn.id","LEFT")
            ->join("cg_kaoheqi1 k1","t.yijikaoheqi=k1.id","LEFT")
            ->join("cg_kaoheqi2 k2","t.erjikaoheqi=k2.id","LEFT")
            ->join("pro_brands pb","t.pinpai=pb.id","LEFT")
            ->join("pro_category pc","t.pinlei=pc.id","LEFT")
            ->join("pro_series ps","t.xilie=ps.id","LEFT")
            ->join("cg_jxs j","t.jingxiao=j.id","LEFT")
            ->where(["t.apply_id"=>$apply["yuanid"]])
            ->field("j.title as jingxiao,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,t.feiyong,pb.brand_name as pinpai,pc.category_name as pinlei,ps.series_name as xilie,t.feiyong")
            ->select();
             $tongjidata=[];
             for($i=0;$i<count($detail);$i++){
                
                $tongji["jingxiao"]=$detail[$i]["jingxiao"];$tongji["pinpai"]=$detail[$i]["pinpai"];$tongji["pinlei"]=$detail[$i]["pinlei"];
                $tongji["xilie"]=$detail[$i]["xilie"];$tongji["yijikaoheqi"]=$detail[$i]["yijikaoheqi"];$tongji["erjikaoheqi"]=$detail[$i]["erjikaoheqi"];
                $tongji["feiyong"]=$detail[$i]["feiyong"];$tongji["cainian"]=$detail[$i]["cainian"];
                $tongji["yuanfeiyong"]=0;
                 for($j=0;$j<count($yuandetail);$j++){
                    if($detail[$i]["yijikaoheqi"]==$yuandetail[$j]["yijikaoheqi"]&&$detail[$i]["erjikaoheqi"]==$yuandetail[$j]["erjikaoheqi"]&&$detail[$i]["cainian"]==$yuandetail[$j]["cainian"]&&$detail[$i]["jingxiao"]==$yuandetail[$j]["jingxiao"]&&$detail[$i]["pinpai"]==$yuandetail[$j]["pinpai"]&&$detail[$i]["pinlei"]==$yuandetail[$j]["pinlei"]&&$detail[$i]["xilie"]==$yuandetail[$j]["xilie"]){
                            //内容相似
                            $tongji["yuanfeiyong"]=$yuandetail[$j]["feiyong"];
                            
                            array_splice($yuandetail, $j, 1);break;
                        }
                      
                 }
                 $tongji["duibi"]=$tongji["feiyong"]-$tongji["yuanfeiyong"];
                 $tongjidata[]=$tongji;
            }
            for($i=0;$i<count($yuandetail);$i++){
                $tongji=["cainian"=>$yuandetail[$i]["cainian"],"yijikaoheqi"=>$yuandetail[$i]["yijikaoheqi"],"erjikaoheqi"=>$yuandetail[$i]["erjikaoheqi"],"jingxiao"=>$yuandetail[$i]["jingxiao"],"pinpai"=>$yuandetail[$i]["pinpai"],"pinlei"=>$yuandetail[$i]["pinlei"],"xilie"=>$yuandetail[$i]["xilie"],"feiyong"=>0,"yuanfeiyong"=>$yuandetail[$i]["feiyong"],"duibi"=>0-$yuandetail[$i]["feiyong"]];
                $tongjidata[]=$tongji;
            }
            //print_r($tongjidata);exit;
            $this->assign("tongjidata",$tongjidata);
        }
		return		$this->fetch("detail");

	}


	 //预算统计查询
    public function tongji($page=1,$list=20){
        $typename="经销商销售目标格式";
        $tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //接收数据
        $request = Request::instance();

        
        $data=$request->post();
        $where="t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.type_id=$type_id and t.tag<>1 ";
        if($data){
            if(trim($data["cainian"]))
                $where.="and ap.cainian='{$data["cainian"]}' ";
           if(trim($data["yijikaoheqi"]))
                $where.="and ap.yijikaoheqi='{$data["yijikaoheqi"]}' ";
            if(trim($data["erjikaoheqi"]))
                $where.="and ap.erjikaoheqi='{$data["erjikaoheqi"]}' ";
            if(trim($data["pinpai"]))
                $where.="and ap.pinpai='{$data["pinpai"]}' ";
            if(trim($data["jingxiao"])){
                $where.="and j.title='{$data["jingxiao"]}' ";
            }
        }else{
            $data=["cainian"=>"","yijikaoheqi"=>"","erjikaoheqi"=>"","pinpai"=>"","jingxiao"=>""];
        }
        //echo $where;
        $sum=Db::table("target_apply")->alias("t")
        ->join("target_apply_detail ap","ap.apply_id=t.id")
        ->join("cg_jxs j","ap.jingxiao=j.id","LEFT")
        ->where($where)
        ->count();
       

        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply_detail ap","ap.apply_id=t.id")
        ->join("cg_jxs j","ap.jingxiao=j.id","LEFT")
        ->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")
        ->join("cg_market_provice p","ap.sheng=p.id","LEFT")
        ->join("cg_market_city c","ap.shi=c.id","LEFT")
         ->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","ap.channel2=c2.id","LEFT")
        
        ->join("cg_system x","ap.xitong=x.id","LEFT")
        ->join("cg_net n","ap.wangdian=n.id","LEFT")
        ->join("pro_brands pb","ap.pinpai=pb.id","LEFT")
        ->join("pro_category pc","ap.pinlei=pc.id","LEFT")
        ->join("pro_series ps","ap.xilie=ps.id","LEFT")
        ->join("cg_feiyongkemu1 f1","ap.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","ap.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","ap.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","ap.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","ap.erjikaoheqi=k2.id","LEFT")
        ->where($where)
        ->order("t.id desc,ap.id desc")
        ->limit($start,$list)
        ->field("t.no,ap.detail_no,c1.title as channel1,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jingxiao,pb.brand_name as pinpai,f1.title as  yijifeiyong,f2.title as erjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,ap.feiyong,ap.sale,ap.sale_real,ap.profit")
        ->select();
        //print_r($apply);
         $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
        //所有财年
        $cainian=Db::table("cg_cainian")->select();
        $this->assign("cainian",$cainian);
        //一级考核期
        $kaohe1=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")
        ->field("c.id as cainian,k1.title,k1.id")->select();
        $this->assign("kaohe1",$kaohe1);
        $jsonkaohe1=json_encode($kaohe1);
        $this->assign("jsonkaohe1",$jsonkaohe1);
         $kaohe2=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->join("cg_cainian c","k1.cid=c.id")
        ->field("c.id as cainian,k1.id as yijikaoheqi,k2.title,k2.id")->select();
          $this->assign("kaohe2",$kaohe2);
           $jsonkaohe2=json_encode($kaohe2);
          $this->assign("jsonkaohe2",$jsonkaohe2);
          //print_r($kaohe2);
        //品牌
        $pinpai=Db::table("pro_brands")->where(["is_deleted"=>0])->select();
        $this->assign("pinpai",$pinpai);
       
      
        return $this->fetch("tongji");
    }
     //预算统计查询
    public function shutongji($page=1,$list=20){
        $typename="经销商销售目标格式";
        $tmp=Db::table("target_format_type")->where(["format_type"=>$typename])->find();
        $type_id=$tmp["id"];
        //接收数据
        $request = Request::instance();

        
        $data=$request->post();
        $where="t.is_actived=1 and t.is_deleted=0 and t.state=7 and t.type_id=$type_id and t.tag<>1 ";
        if($data){
            if(trim($data["cainian"]))
                $where.="and ap.cainian='{$data["cainian"]}' ";
           if(trim($data["yijikaoheqi"]))
                $where.="and ap.yijikaoheqi='{$data["yijikaoheqi"]}' ";
            if(trim($data["erjikaoheqi"]))
                $where.="and ap.erjikaoheqi='{$data["erjikaoheqi"]}' ";
            if(trim($data["pinpai"]))
                $where.="and ap.pinpai='{$data["pinpai"]}' ";
            if(trim($data["jingxiao"])){
                $where.="and j.title='{$data["jingxiao"]}' ";
            }
        }else{
            $data=["cainian"=>"","yijikaoheqi"=>"","erjikaoheqi"=>"","pinpai"=>"","jingxiao"=>""];
        }
        //echo $where;
        $sum=Db::table("target_apply")->alias("t")
        ->join("target_apply_detail ap","ap.apply_id=t.id")
        ->join("cg_jxs j","ap.jingxiao=j.id","LEFT")
        ->where($where)
        ->count();
       

        $maxpage=ceil($sum/$list);
        $page=$page<$maxpage?$page:$maxpage;
        $page=$page>1?$page:1;

        $start=($page-1)*$list;
        $apply=Db::table("target_apply")->alias("t")
        ->join("target_apply_detail ap","ap.apply_id=t.id")
        ->join("cg_jxs j","ap.jingxiao=j.id","LEFT")
        ->join("cg_market_zoning z","ap.fenqu=z.id","LEFT")
        ->join("cg_market_provice p","ap.sheng=p.id","LEFT")
        ->join("cg_market_city c","ap.shi=c.id","LEFT")
         ->join("cg_channel_types1 c1","ap.channel1=c1.id","LEFT")
        ->join("cg_channel_types2 c2","ap.channel2=c2.id","LEFT")
        
        ->join("cg_system x","ap.xitong=x.id","LEFT")
        ->join("cg_net n","ap.wangdian=n.id","LEFT")
        ->join("pro_brands pb","ap.pinpai=pb.id","LEFT")
        ->join("pro_category pc","ap.pinlei=pc.id","LEFT")
        ->join("pro_series ps","ap.xilie=ps.id","LEFT")
        ->join("cg_feiyongkemu1 f1","ap.yijifeiyong=f1.id","LEFT")
        ->join("cg_feiyongkemu2 f2","ap.erjifeiyong=f2.id","LEFT")
        ->join("cg_feiyongkemu3 f3","ap.sanjifeiyong=f3.id","LEFT")
        ->join("cg_cainian cn","ap.cainian=cn.id","LEFT")
        ->join("cg_kaoheqi1 k1","ap.yijikaoheqi=k1.id","LEFT")
        ->join("cg_kaoheqi2 k2","ap.erjikaoheqi=k2.id","LEFT")
        ->where($where)
        ->order("t.id desc,ap.id desc")
        ->limit($start,$list)
        ->field("t.no,ap.detail_no,c1.title as channel1,z.title as fenqu,p.title as sheng,c.title as shi,j.title as jingxiao,pb.brand_name as pinpai,f1.title as  yijifeiyong,f2.title as erjifeiyong,cn.title as cainian,k1.title as yijikaoheqi,k2.title as erjikaoheqi,ap.feiyong,ap.sale,ap.sale_real,ap.profit")
        ->select();
        //print_r($apply);
         $this->assign("sum",$sum);
        $this->assign("page",$page);
        $this->assign("list",$list);
        $this->assign("maxpage",$maxpage);
        $this->assign("data",$data);
        $this->assign("apply",$apply);
        //所有财年
        $cainian=Db::table("cg_cainian")->select();
        $this->assign("cainian",$cainian);
        //一级考核期
        $kaohe1=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_cainian c","k1.cid=c.id")
        ->field("c.id as cainian,k1.title,k1.id")->select();
        $this->assign("kaohe1",$kaohe1);
        $jsonkaohe1=json_encode($kaohe1);
        $this->assign("jsonkaohe1",$jsonkaohe1);
         $kaohe2=Db::table("cg_kaoheqi1")->alias("k1")->join("cg_kaoheqi2 k2","k2.pid=k1.id")->join("cg_cainian c","k1.cid=c.id")
        ->field("c.id as cainian,k1.id as yijikaoheqi,k2.title,k2.id")->select();
          $this->assign("kaohe2",$kaohe2);
           $jsonkaohe2=json_encode($kaohe2);
          $this->assign("jsonkaohe2",$jsonkaohe2);
          //print_r($kaohe2);
        //品牌
        $pinpai=Db::table("pro_brands")->where(["is_deleted"=>0])->select();
        $this->assign("pinpai",$pinpai);
       
      
        return $this->fetch();
    }
}